const express = require('express');
const router = express.Router();
const db = require('../config/database');
const { authenticateToken } = require('../middleware/auth');

router.use(authenticateToken);

// ========================================
// 1. 财务目标管理
// ========================================

// 获取目标列表
router.get('/goals', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const { status } = req.query;

        let query = 'SELECT * FROM financial_goals WHERE user_id = ?';
        const params = [userId];

        if (status) {
            query += ' AND status = ?';
            params.push(status);
        }

        query += ' ORDER BY deadline ASC';

        const [goals] = await db.query(query, params);

        res.json({ code: 200, data: goals });
    } catch (error) {
        console.error('获取目标失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// 创建目标
router.post('/goals', async (req, res) => {
    try {
        const { goal_name, goal_type, target_amount, start_date, deadline, description, book_id } = req.body;
        const userId = req.user.user_id;

        const [result] = await db.query(
            `INSERT INTO financial_goals (user_id, book_id, goal_name, goal_type, target_amount, start_date, deadline, description)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [userId, book_id || null, goal_name, goal_type || 'saving', target_amount, start_date, deadline, description]
        );

        res.json({ code: 200, message: '目标创建成功', data: { goal_id: result.insertId } });
    } catch (error) {
        console.error('创建目标失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// 更新目标进度
router.put('/goals/:id/progress', async (req, res) => {
    try {
        const { id } = req.params;
        const { current_amount } = req.body;
        const userId = req.user.user_id;

        // 获取目标信息
        const [[goal]] = await db.query('SELECT * FROM financial_goals WHERE goal_id = ? AND user_id = ?', [id, userId]);
        
        if (!goal) {
            return res.status(404).json({ code: 404, message: '目标不存在' });
        }

        const progress = (current_amount / goal.target_amount * 100).toFixed(2);
        const status = current_amount >= goal.target_amount ? 'completed' : 'active';

        await db.query(
            `UPDATE financial_goals SET current_amount = ?, progress_percent = ?, status = ? WHERE goal_id = ?`,
            [current_amount, progress, status, id]
        );

        res.json({ code: 200, message: '进度更新成功' });
    } catch (error) {
        console.error('更新进度失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// ========================================
// 2. 定期账单管理
// ========================================

// 获取定期账单列表
router.get('/recurring-bills', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const [bills] = await db.query(
            'SELECT * FROM recurring_bills WHERE user_id = ? ORDER BY next_date ASC',
            [userId]
        );
        res.json({ code: 200, data: bills });
    } catch (error) {
        console.error('获取定期账单失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// 创建定期账单
router.post('/recurring-bills', async (req, res) => {
    try {
        const { book_id, bill_name, type, amount, category, recurrence_type, recurrence_day, start_date, auto_create } = req.body;
        const userId = req.user.user_id;

        // 计算下次生成日期
        let next_date = start_date;
        
        const [result] = await db.query(
            `INSERT INTO recurring_bills (user_id, book_id, bill_name, type, amount, category, recurrence_type, recurrence_day, start_date, next_date, auto_create)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [userId, book_id, bill_name, type, amount, category, recurrence_type, recurrence_day || null, start_date, next_date, auto_create || 0]
        );

        res.json({ code: 200, message: '定期账单创建成功', data: { bill_id: result.insertId } });
    } catch (error) {
        console.error('创建定期账单失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// 从定期账单创建记录
router.post('/recurring-bills/:id/create-record', async (req, res) => {
    try {
        const { id } = req.params;
        const userId = req.user.user_id;

        const [[bill]] = await db.query('SELECT * FROM recurring_bills WHERE bill_id = ? AND user_id = ?', [id, userId]);
        
        if (!bill) {
            return res.status(404).json({ code: 404, message: '账单不存在' });
        }

        // 创建记录
        await db.query(
            `INSERT INTO records (user_id, book_id, type, amount, category, date, remark, from_recurring, recurring_bill_id)
             VALUES (?, ?, ?, ?, ?, CURDATE(), ?, 1, ?)`,
            [userId, bill.book_id, bill.type, bill.amount, bill.category, bill.bill_name, id]
        );

        res.json({ code: 200, message: '记录创建成功' });
    } catch (error) {
        console.error('创建记录失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// ========================================
// 3. 账本留言板
// ========================================

// 获取留言列表
router.get('/comments', async (req, res) => {
    try {
        const { book_id } = req.query;
        
        const [comments] = await db.query(
            `SELECT c.*, u.username, u.nickname 
             FROM book_comments c
             JOIN users u ON c.user_id = u.user_id
             WHERE c.book_id = ?
             ORDER BY c.created_at DESC`,
            [book_id]
        );

        res.json({ code: 200, data: comments });
    } catch (error) {
        console.error('获取留言失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// 添加留言
router.post('/comments', async (req, res) => {
    try {
        const { book_id, content, record_id } = req.body;
        const userId = req.user.user_id;

        const [result] = await db.query(
            'INSERT INTO book_comments (book_id, user_id, record_id, content) VALUES (?, ?, ?, ?)',
            [book_id, userId, record_id || null, content]
        );

        res.json({ code: 200, message: '留言成功', data: { comment_id: result.insertId } });
    } catch (error) {
        console.error('添加留言失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// ========================================
// 4. 成就系统
// ========================================

// 获取用户成就
router.get('/achievements', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const [achievements] = await db.query(
            'SELECT * FROM user_achievements WHERE user_id = ? ORDER BY achieved_at DESC',
            [userId]
        );
        res.json({ code: 200, data: achievements });
    } catch (error) {
        console.error('获取成就失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// 检查并解锁成就
router.post('/achievements/check', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const newAchievements = [];

        // 获取用户统计
        const [[stats]] = await db.query('SELECT * FROM v_user_stats WHERE user_id = ?', [userId]);

        // 检查各种成就
        const achievementRules = [
            { type: 'first_record', name: '🎉 首次记账', desc: '添加了第一条记录', condition: stats.total_records >= 1 },
            { type: 'record_10', name: '📝 记账新手', desc: '累计记账10条', condition: stats.total_records >= 10 },
            { type: 'record_100', name: '📚 记账达人', desc: '累计记账100条', condition: stats.total_records >= 100 },
            { type: 'record_1000', name: '🏆 记账专家', desc: '累计记账1000条', condition: stats.total_records >= 1000 },
            { type: 'multi_book', name: '📖 多账本管理者', desc: '创建了多个账本', condition: stats.total_books >= 3 }
        ];

        for (const rule of achievementRules) {
            if (rule.condition) {
                // 检查是否已获得
                const [[existing]] = await db.query(
                    'SELECT * FROM user_achievements WHERE user_id = ? AND achievement_type = ?',
                    [userId, rule.type]
                );

                if (!existing) {
                    await db.query(
                        'INSERT INTO user_achievements (user_id, achievement_type, achievement_name, achievement_desc) VALUES (?, ?, ?, ?)',
                        [userId, rule.type, rule.name, rule.desc]
                    );
                    newAchievements.push(rule);
                }
            }
        }

        res.json({ code: 200, data: { new_achievements: newAchievements } });
    } catch (error) {
        console.error('检查成就失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// ========================================
// 5. 智能分类建议
// ========================================

// 获取分类建议
router.get('/category-suggest', async (req, res) => {
    try {
        const { keyword } = req.query;
        const userId = req.user.user_id;

        if (!keyword) {
            return res.json({ code: 200, data: [] });
        }

        // 先查用户自己的习惯
        const [userSuggestions] = await db.query(
            'SELECT * FROM category_suggestions WHERE user_id = ? AND keyword LIKE ? ORDER BY use_count DESC LIMIT 5',
            [userId, `%${keyword}%`]
        );

        // 内置关键词库
        const builtInKeywords = {
            '早餐': 'food', '午餐': 'food', '晚餐': 'food', '麦当劳': 'food', '肯德基': 'food',
            '地铁': 'transport', '公交': 'transport', '打车': 'transport', '滴滴': 'transport',
            '淘宝': 'shopping', '京东': 'shopping', '超市': 'shopping',
            '电影': 'entertainment', 'KTV': 'entertainment',
            '房租': 'housing', '水费': 'utilities', '电费': 'utilities'
        };

        const suggestions = [];
        
        // 用户习惯优先
        userSuggestions.forEach(s => {
            suggestions.push({ category: s.suggested_category, source: 'user' });
        });

        // 内置关键词
        Object.keys(builtInKeywords).forEach(key => {
            if (key.includes(keyword) && suggestions.length < 5) {
                suggestions.push({ category: builtInKeywords[key], source: 'built-in' });
            }
        });

        res.json({ code: 200, data: suggestions });
    } catch (error) {
        console.error('获取建议失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// 学习用户习惯
router.post('/category-suggest/learn', async (req, res) => {
    try {
        const { keyword, category } = req.body;
        const userId = req.user.user_id;

        await db.query(
            `INSERT INTO category_suggestions (user_id, keyword, suggested_category, use_count)
             VALUES (?, ?, ?, 1)
             ON DUPLICATE KEY UPDATE use_count = use_count + 1`,
            [userId, keyword, category]
        );

        res.json({ code: 200, message: '学习成功' });
    } catch (error) {
        console.error('学习失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// ========================================
// 6. 消费分析报告
// ========================================

// 生成分析报告
router.post('/analysis/generate', async (req, res) => {
    try {
        const { book_id, report_type } = req.body;
        const userId = req.user.user_id;

        // 确定时间范围
        const now = new Date();
        let startDate, endDate, period;

        if (report_type === 'weekly') {
            const day = now.getDay();
            const diff = now.getDate() - day + (day === 0 ? -6 : 1);
            startDate = new Date(now.setDate(diff)).toISOString().split('T')[0];
            endDate = new Date(now.setDate(diff + 6)).toISOString().split('T')[0];
            period = startDate;
        } else if (report_type === 'monthly') {
            startDate = new Date(now.getFullYear(), now.getMonth(), 1).toISOString().split('T')[0];
            endDate = new Date(now.getFullYear(), now.getMonth() + 1, 0).toISOString().split('T')[0];
            period = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}`;
        } else {
            startDate = new Date(now.getFullYear(), 0, 1).toISOString().split('T')[0];
            endDate = new Date(now.getFullYear(), 11, 31).toISOString().split('T')[0];
            period = now.getFullYear().toString();
        }

        // 查询数据
        const [[summary]] = await db.query(
            `SELECT 
                COALESCE(SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END), 0) as total_income,
                COALESCE(SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END), 0) as total_expense,
                COUNT(*) as record_count
             FROM records
             WHERE user_id = ? AND book_id = ? AND is_deleted = 0 AND date BETWEEN ? AND ?`,
            [userId, book_id, startDate, endDate]
        );

        // 最高支出分类
        const [[topCategory]] = await db.query(
            `SELECT category, SUM(amount) as total
             FROM records
             WHERE user_id = ? AND book_id = ? AND type = 'expense' AND is_deleted = 0 AND date BETWEEN ? AND ?
             GROUP BY category
             ORDER BY total DESC
             LIMIT 1`,
            [userId, book_id, startDate, endDate]
        );

        // 计算日均支出
        const days = Math.ceil((new Date(endDate) - new Date(startDate)) / (1000 * 60 * 60 * 24)) + 1;
        const avgDailyExpense = (summary.total_expense / days).toFixed(2);

        // 生成AI建议
        const insights = [];
        if (summary.total_expense > summary.total_income) {
            insights.push('⚠️ 支出大于收入，建议控制消费');
        }
        if (avgDailyExpense > 100) {
            insights.push('💡 日均支出较高，可以考虑设置预算');
        }
        if (topCategory) {
            insights.push(`📊 最高支出分类：${topCategory.category}（¥${topCategory.total}）`);
        }

        // 保存报告
        await db.query(
            `INSERT INTO analysis_reports (user_id, book_id, report_type, report_period, total_income, total_expense, record_count, top_category, avg_daily_expense, insights)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
             ON DUPLICATE KEY UPDATE
                total_income = VALUES(total_income),
                total_expense = VALUES(total_expense),
                record_count = VALUES(record_count),
                top_category = VALUES(top_category),
                avg_daily_expense = VALUES(avg_daily_expense),
                insights = VALUES(insights)`,
            [userId, book_id, report_type, period, summary.total_income, summary.total_expense, summary.record_count, topCategory?.category, avgDailyExpense, JSON.stringify(insights)]
        );

        res.json({
            code: 200,
            message: '报告生成成功',
            data: {
                ...summary,
                top_category: topCategory?.category,
                avg_daily_expense: avgDailyExpense,
                insights,
                period: { start: startDate, end: endDate }
            }
        });
    } catch (error) {
        console.error('生成报告失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// 获取历史报告
router.get('/analysis/reports', async (req, res) => {
    try {
        const { report_type } = req.query;
        const userId = req.user.user_id;

        let query = 'SELECT * FROM analysis_reports WHERE user_id = ?';
        const params = [userId];

        if (report_type) {
            query += ' AND report_type = ?';
            params.push(report_type);
        }

        query += ' ORDER BY created_at DESC LIMIT 10';

        const [reports] = await db.query(query, params);

        // 解析insights JSON
        reports.forEach(report => {
            if (report.insights) {
                try {
                    report.insights = JSON.parse(report.insights);
                } catch (e) {
                    report.insights = [];
                }
            }
        });

        res.json({ code: 200, data: reports });
    } catch (error) {
        console.error('获取报告失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// ========================================
// 7. 数据备份
// ========================================

// 创建备份
router.post('/backup', async (req, res) => {
    try {
        const userId = req.user.user_id;

        // 获取所有数据
        const [records] = await db.query(
            'SELECT * FROM records WHERE user_id = ? AND is_deleted = 0',
            [userId]
        );

        const [books] = await db.query(
            'SELECT * FROM account_books WHERE owner_user_id = ?',
            [userId]
        );

        const backupData = {
            records,
            books,
            backup_time: new Date().toISOString(),
            version: '1.0'
        };

        const backupJson = JSON.stringify(backupData, null, 2);
        const backupSize = Buffer.byteLength(backupJson, 'utf8');

        // 记录备份日志
        await db.query(
            'INSERT INTO backup_logs (user_id, backup_type, backup_size, record_count) VALUES (?, ?, ?, ?)',
            [userId, 'manual', backupSize, records.length]
        );

        // 返回备份数据
        res.setHeader('Content-Type', 'application/json; charset=utf-8');
        res.setHeader('Content-Disposition', `attachment; filename="backup_${Date.now()}.json"`);
        res.send(backupJson);
    } catch (error) {
        console.error('备份失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

// ========================================
// 8. 排行榜
// ========================================

// 获取账本记账排行榜
router.get('/leaderboard', async (req, res) => {
    try {
        const { book_id } = req.query;

        const [ranking] = await db.query(
            `SELECT 
                u.user_id,
                u.username,
                u.nickname,
                COUNT(r.record_id) as record_count,
                COALESCE(SUM(r.amount), 0) as total_amount
             FROM users u
             LEFT JOIN records r ON u.user_id = r.user_id AND r.book_id = ? AND r.is_deleted = 0
             WHERE EXISTS (
                 SELECT 1 FROM book_members WHERE book_id = ? AND user_id = u.user_id
             ) OR u.user_id IN (
                 SELECT owner_user_id FROM account_books WHERE book_id = ?
             )
             GROUP BY u.user_id
             ORDER BY record_count DESC`,
            [book_id, book_id, book_id]
        );

        res.json({ code: 200, data: ranking });
    } catch (error) {
        console.error('获取排行榜失败:', error);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});

module.exports = router; 